package com.excel.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import javax.transaction.Transactional;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.entity.*;
import com.file.service.*;
import com.interfaces.*;
import com.utils.JsonResult;

/**
 * @Title: ExcelImportService.java
 * @Package com.excel.service
 * @author 孙梦楠
 * @date 2017年11月3日 上午8:42:58
 * @version V1.0.0
 */
@Service
public class ExcelImportService {

	@Autowired
	private CustomerService customerservice;

	@Autowired
	private MeterService meterservice;

	@Autowired
	private TerminalService terminalservice;

	@Autowired
	private CollectorService collectorservice;

	@Autowired
	private CustomerMeterService customermeterservice;

	@Autowired
	private MeasuringpointService measuringpointservice;

	@Autowired
	private CustomerDao customerdao;

	@Autowired
	private TerminalDao terminaldao;

	@Autowired
	private CollectorDao collectordao;

	@Autowired
	private MeterDao meterdao;

	private static final Logger log = LoggerFactory.getLogger(ExcelImportService.class);
	int customercount=0;
	int metercount=0;
	int terminalcount=0;
	int collectorcount=0;

	@Transactional
	public JsonResult<String> excelImport(String filename, String opeorgcookie, String opetypecookie,
			String languagecookie) throws FileNotFoundException, IOException {

		File excelFile = new File(filename);
		if (filename.endsWith("xls")) {// excel 2007 版
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
			HSSFSheet sheet = wb.getSheetAt(0);
			for (Row row : sheet) {
				if (row.getRowNum() > 0) {
					importdate(opeorgcookie, opetypecookie, languagecookie, row);
				}

			}
			log.info("新增用户成功，共增加了" + customercount + "个用户");
			log.info("新增终端成功，共增加了" + terminalcount + "个终端");
			log.info("新增采集器成功，共增加了" + collectorcount + "个采集器");
			log.info("新增表计成功，共增加了" + metercount + "个表计");
			wb.close();
			excelFile.delete();
			return new JsonResult<String>(0, "导入成功-xls", null);
		} else if (filename.endsWith("xlsx")) {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(excelFile));
			XSSFSheet sheet = wb.getSheetAt(0);
			for (Row row : sheet) {
				if (row.getRowNum() > 0) {
					importdate(opeorgcookie, opetypecookie, languagecookie, row);
				}
			}
			log.info("新增用户成功，共增加了" + customercount + "个用户");
			log.info("新增终端成功，共增加了" + terminalcount + "个终端");
			log.info("新增采集器成功，共增加了" + collectorcount + "个采集器");
			log.info("新增表计成功，共增加了" + metercount + "个表计");
			wb.close();
			excelFile.delete();
			return new JsonResult<String>(0, "导入成功-xlsx", null);
		} else
			return new JsonResult<String>(0, "导入失败，文件格式出错", null);

	}

	private void importdate(String opeorgcookie, String opetypecookie, String languagecookie, Row row) {
		// 增加用户
		Integer customerid = importcustomer(row, opeorgcookie, opetypecookie, languagecookie);
		// 增加终端
		Integer terminalid = importterminal(row, opeorgcookie, opetypecookie, languagecookie);
		// 增加采集器
		Integer collectorid = null;
		if (row.getCell(10) != null) {
			collectorid = importcollector(row, opeorgcookie, opetypecookie, languagecookie);
		}
		// 增加表计
		Integer meterid = importmeter(row, opeorgcookie, opetypecookie, languagecookie);
		//用户与表计关联关系
		customermeterservice.addcustomermeter(customerid, meterid);
		//测量点关联关系
		importmeasuringpoint(row, terminalid, collectorid, meterid);
	}

	// 导入用户信息
	@Transactional
	private Integer importcustomer(Row row, String opeorgcookie, String opetypecookie, String languagecookie) {

		// 用户名
		String customername = row.getCell(0).getRichStringCellValue().getString();
		// 用户地址
		String customeraddress = row.getCell(1).getRichStringCellValue().getString();
		// 用户手机号
		String customerphone = row.getCell(2).getRichStringCellValue().getString();
		// // 用户微信号（有NULL的判断）
		// String customerwechat = null;
		// // 判断null
		// Cell c = row.getCell(3, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
		// if (c != null) {
		// customerwechat = row.getCell(3).getRichStringCellValue().getString();
		// }
		//
		// 增加用户
		if(customerdao.findByConditions(null, customername, customeraddress, opetypecookie,
				Integer.valueOf(opeorgcookie), null, customerphone, null).size()==0) {
		customerservice.addcustomer(customername, customeraddress, opetypecookie, opeorgcookie, customerphone, null);
		customercount++;
		}
		Integer customerid = customerdao.findByConditions(null, customername, customeraddress, opetypecookie,
				Integer.valueOf(opeorgcookie), null, customerphone, null).get(0).getCustomerid();

		return customerid;
	}

	// 导入终端信息
	@Transactional
	private Integer importterminal(Row row, String opeorgcookie, String opetypecookie, String languagecookie) {

		// 终端出厂编号
		String terminalcode = row.getCell(3).getRichStringCellValue().getString();
		// 终端资产编号
		String terminalassetcode = row.getCell(4).getRichStringCellValue().getString();
		// 终端类型
		String terminaltype = row.getCell(5).getRichStringCellValue().getString();
		// 终端设备安装地址
		String terminalphysicaladdress = row.getCell(6).getRichStringCellValue().getString();
		// 终端通讯地址
		String terminaladdress = row.getCell(7).getRichStringCellValue().getString();
		// 终端通讯方式
		String terminalcommuncate = row.getCell(8).getRichStringCellValue().getString();
		// 终端制造单位
		String terminalmade = row.getCell(9).getRichStringCellValue().getString();

		if (terminaldao.findByTerminaladdress(terminaladdress) == null) {
			terminalservice.addTerminal(terminalcode, terminalassetcode, terminaltype, terminaladdress,
					terminalcommuncate, terminalmade, opeorgcookie, opetypecookie, terminalphysicaladdress);
			terminalcount++;
		}
		Integer terminalid = terminaldao.findByTerminaladdress(terminaladdress).getTerminalid();

		return terminalid;

	}

	// 导入采集器信息
	@Transactional
	private Integer importcollector(Row row, String opeorgcookie, String opetypecookie, String languagecookie) {

		// 采集器出厂编码
		String collectorcode = row.getCell(10).getRichStringCellValue().getString();
		// 采集器资产编码
		String collectorassetcode = row.getCell(11).getRichStringCellValue().getString();
		// 采集器类型
		String collectortype = row.getCell(12).getRichStringCellValue().getString();
		// 采集器通信地址
		String collectoraddress = row.getCell(13).getRichStringCellValue().getString();
		// 采集器协议
		String collectorprotocol = row.getCell(14).getRichStringCellValue().getString();
		// 采集器制造单位
		String collectormade = row.getCell(15).getRichStringCellValue().getString();
		if (collectordao.findByCollectorcodeAndCollectorstatusNot(collectorcode, "4") == null) {
			collectorservice.addcollector(collectorcode, collectorassetcode, collectortype, collectoraddress,
					collectorprotocol, collectormade, opeorgcookie, opetypecookie);
			collectorcount++;
		}
		Integer collectorid = collectordao.findByCollectorcodeAndCollectorstatusNot(collectorcode, "4")
				.getCollectorid();
		return collectorid;
	}

	// 导入表计信息
	@Transactional
	private Integer importmeter(Row row, String opeorgcookie, String opetypecookie, String languagecookie) {

		// 表出厂编号
		String metercode = row.getCell(16).getRichStringCellValue().getString();
		// 表资产编号
		String meterassetcode = row.getCell(17).getRichStringCellValue().getString();
		// 表类别
		String metertype = row.getCell(18).getRichStringCellValue().getString();
		// 表通讯地址
		String meteraddress = row.getCell(19).getRichStringCellValue().getString();
		// 表安装地址
		String meterphysicaladdress = row.getCell(20).getRichStringCellValue().getString();
		// 表协议
		String meterprotocol = row.getCell(21).getRichStringCellValue().getString();
		// 表制造单位
		String metermade = row.getCell(22).getRichStringCellValue().getString();
		// 表密码
		String meterpassword = row.getCell(23).getRichStringCellValue().getString();
		// 倍率
		String metermultiple = row.getCell(24).getRichStringCellValue().getString();
		//密钥类型
		 String meterkeytype =  row.getCell(25).getRichStringCellValue().getString();
		

		if(meterdao.findByMeteraddressAndMeterstatusNot(meteraddress, "4")==null){
		meterservice.addmeter(metercode, meterassetcode, metertype, meteraddress, meterprotocol, metermade,
				opeorgcookie, meterpassword, opetypecookie, Integer.valueOf(metermultiple), meterphysicaladdress, meterkeytype);
		metercount++;
		}
		Integer meterid = meterdao.findByMeteraddressAndMeterstatusNot(meteraddress, "4").getMeterid();
		return meterid;
	}

	// 导入采集点关系
	@Transactional
	private void importmeasuringpoint(Row row, Integer terminalid, Integer collectorid, Integer meterid) {

		String measuringpointnumber = row.getCell(26).getRichStringCellValue().getString();
		String measuringpointordernumber = row.getCell(27).getRichStringCellValue().getString();
		String measuringpointport = row.getCell(28).getRichStringCellValue().getString();
		String measuringpointbaud = row.getCell(29).getRichStringCellValue().getString();
		String measuringpointbigint = row.getCell(30).getRichStringCellValue().getString();
		String measuringpointsmallint = row.getCell(31).getRichStringCellValue().getString();
		String measuringpointint = row.getCell(32).getRichStringCellValue().getString();
		String measuringpointdec = row.getCell(33).getRichStringCellValue().getString();

		measuringpointservice.addmeasuringpoint(terminalid, meterid, collectorid, measuringpointnumber,
				measuringpointordernumber, measuringpointport, measuringpointbaud, measuringpointbigint,
				measuringpointsmallint, measuringpointint, measuringpointdec);

	}

}
